﻿namespace Chain.IDAL
{
    using Chain.DBUtility;
    using Chain.Model;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class MemCount
    {
        public int Add(Chain.Model.MemCount model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into MemCount(");
            builder.Append("CountMemID,CountAccount,CountTotalMoney,CountDiscountMoney,CountPayCard,CountPayCash,CountPayCoupon,CountPayType,CountPoint,CountRemark,CountShopID,CountCreateTime,CountUserID,CountIsCard,CountIsCash,CountIsBink,CountPayBink)");
            builder.Append(" values (");
            builder.Append("@CountMemID,@CountAccount,@CountTotalMoney,@CountDiscountMoney,@CountPayCard,@CountPayCash,@CountPayCoupon,@CountPayType,@CountPoint,@CountRemark,@CountShopID,@CountCreateTime,@CountUserID,@CountIsCard,@CountIsCash,@CountIsBink,@CountPayBink)");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@CountMemID", SqlDbType.Int, 4), new SqlParameter("@CountAccount", SqlDbType.VarChar, 50), new SqlParameter("@CountTotalMoney", SqlDbType.Money, 8), new SqlParameter("@CountDiscountMoney", SqlDbType.Money, 8), new SqlParameter("@CountPayCard", SqlDbType.Money, 8), new SqlParameter("@CountPayCash", SqlDbType.Money, 8), new SqlParameter("@CountPayCoupon", SqlDbType.Money, 8), new SqlParameter("@CountPayType", SqlDbType.TinyInt, 1), new SqlParameter("@CountPoint", SqlDbType.Int, 4), new SqlParameter("@CountRemark", SqlDbType.NVarChar, 500), new SqlParameter("@CountShopID", SqlDbType.Int, 4), new SqlParameter("@CountCreateTime", SqlDbType.DateTime), new SqlParameter("@CountUserID", SqlDbType.Int, 4), new SqlParameter("@CountIsCard", SqlDbType.Bit), new SqlParameter("@CountIsCash", SqlDbType.Bit), new SqlParameter("@CountIsBink", SqlDbType.Bit), 
                new SqlParameter("@CountPayBink", SqlDbType.Money, 8)
             };
            cmdParms[0].Value = model.CountMemID;
            cmdParms[1].Value = model.CountAccount;
            cmdParms[2].Value = model.CountTotalMoney;
            cmdParms[3].Value = model.CountDiscountMoney;
            cmdParms[4].Value = model.CountPayCard;
            cmdParms[5].Value = model.CountPayCash;
            cmdParms[6].Value = model.CountPayCoupon;
            cmdParms[7].Value = model.CountPayType;
            cmdParms[8].Value = model.CountPoint;
            cmdParms[9].Value = model.CountRemark;
            cmdParms[10].Value = model.CountShopID;
            cmdParms[11].Value = model.CountCreateTime;
            cmdParms[12].Value = model.CountUserID;
            cmdParms[13].Value = model.CountIsCard;
            cmdParms[14].Value = model.CountIsCash;
            cmdParms[15].Value = model.CountIsBink;
            cmdParms[0x10].Value = model.CountPayBink;
            object single = DbHelperSQL.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int CountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from MemCount ");
            builder.Append(" where CountID=@CountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@CountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = CountID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string CountIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from MemCount ");
            builder.Append(" where CountID in (" + CountIDlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int CountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from MemCount");
            builder.Append(" where CountID=@CountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@CountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = CountID;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public bool Exists(string account)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from MemCount");
            builder.Append(" where CountAccount=@CountAccount");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@CountAccount", SqlDbType.VarChar, 50) };
            cmdParms[0].Value = account;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public DataSet GetCountMoney(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select isnull(sum(CountTotalMoney),0) as TotalMoney,isnull(sum(CountDiscountMoney),0) as DiscountMoney,isnull(sum(CountPoint),0) as TotalPoint FROM MemCount,Mem ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where MemCount.CountMemID=Mem.MemID and " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetCountNumber(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select isnull(sum(MemCountDetail.CountDetailTotalNumber),0) as TotalNumber,isnull(sum(MemCountDetail.CountDetailNumber),0) as RemainCount from MemCountDetail,MemCount,Mem ");
            builder.Append(" where  MemCount.CountID=MemCountDetail.CountDetailCountID and MemCount.CountMemID=Mem.MemID");
            if (strWhere.Trim() != "")
            {
                builder.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select CountID,CountMemID,CountAccount,CountTotalMoney,CountDiscountMoney,CountPayCard,CountPayCash,CountPayCoupon,CountPayType,CountPoint,CountRemark,CountShopID,CountCreateTime,CountUserID,CountIsCard,CountIsCash,CountIsBink,CountPayBink ");
            builder.Append(" FROM MemCount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" CountID,CountMemID,CountAccount,CountTotalMoney,CountDiscountMoney,CountPayCard,CountPayCash,CountPayCoupon,CountPayType,CountPoint,CountRemark,CountShopID,CountCreateTime,CountUserID,CountIsCard,CountIsCash,CountIsBink,CountPayBink ");
            builder.Append(" FROM MemCount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.CountID desc");
            }
            builder.Append(")AS Row, T.*  from MemCount T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetListSP(int PageSize, int PageIndex, out int resCount, params string[] strWhere)
        {
            string tableName = "Mem,MemCount,SysShop,SysUser,MemLevel";
            string[] column = new string[] { "Mem.MemCard,Mem.MemName,SysShop.ShopName,MemCount.*,SysUser.UserName" };
            string[] condition = strWhere;
            int recordCount = 1;
            DataSet set = DbHelperSQL.GetTable(tableName, column, condition, "CountID", "CountID", false, PageSize, PageIndex, recordCount);
            resCount = int.Parse(set.Tables[1].Rows[0][0].ToString());
            return set;
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("CountID", "MemCount");
        }

        public Chain.Model.MemCount GetModel(int CountID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 CountID,CountMemID,CountAccount,CountTotalMoney,CountDiscountMoney,CountPayCard,CountPayCash,CountPayCoupon,CountPayType,CountPoint,CountRemark,CountShopID,CountCreateTime,CountUserID,CountIsCard,CountIsCash,CountIsBink,CountPayBink from MemCount ");
            builder.Append(" where CountID=@CountID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@CountID", SqlDbType.Int, 4) };
            cmdParms[0].Value = CountID;
            Chain.Model.MemCount count = new Chain.Model.MemCount();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count <= 0)
            {
                return null;
            }
            if ((set.Tables[0].Rows[0]["CountID"] != null) && (set.Tables[0].Rows[0]["CountID"].ToString() != ""))
            {
                count.CountID = int.Parse(set.Tables[0].Rows[0]["CountID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountMemID"] != null) && (set.Tables[0].Rows[0]["CountMemID"].ToString() != ""))
            {
                count.CountMemID = int.Parse(set.Tables[0].Rows[0]["CountMemID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountAccount"] != null) && (set.Tables[0].Rows[0]["CountAccount"].ToString() != ""))
            {
                count.CountAccount = set.Tables[0].Rows[0]["CountAccount"].ToString();
            }
            if ((set.Tables[0].Rows[0]["CountTotalMoney"] != null) && (set.Tables[0].Rows[0]["CountTotalMoney"].ToString() != ""))
            {
                count.CountTotalMoney = decimal.Parse(set.Tables[0].Rows[0]["CountTotalMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountDiscountMoney"] != null) && (set.Tables[0].Rows[0]["CountDiscountMoney"].ToString() != ""))
            {
                count.CountDiscountMoney = decimal.Parse(set.Tables[0].Rows[0]["CountDiscountMoney"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountPayCard"] != null) && (set.Tables[0].Rows[0]["CountPayCard"].ToString() != ""))
            {
                count.CountPayCard = decimal.Parse(set.Tables[0].Rows[0]["CountPayCard"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountPayCash"] != null) && (set.Tables[0].Rows[0]["CountPayCash"].ToString() != ""))
            {
                count.CountPayCash = decimal.Parse(set.Tables[0].Rows[0]["CountPayCash"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountPayCoupon"] != null) && (set.Tables[0].Rows[0]["CountPayCoupon"].ToString() != ""))
            {
                count.CountPayCoupon = decimal.Parse(set.Tables[0].Rows[0]["CountPayCoupon"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountPayType"] != null) && (set.Tables[0].Rows[0]["CountPayType"].ToString() != ""))
            {
                count.CountPayType = int.Parse(set.Tables[0].Rows[0]["CountPayType"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountPoint"] != null) && (set.Tables[0].Rows[0]["CountPoint"].ToString() != ""))
            {
                count.CountPoint = int.Parse(set.Tables[0].Rows[0]["CountPoint"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountRemark"] != null) && (set.Tables[0].Rows[0]["CountRemark"].ToString() != ""))
            {
                count.CountRemark = set.Tables[0].Rows[0]["CountRemark"].ToString();
            }
            if ((set.Tables[0].Rows[0]["CountShopID"] != null) && (set.Tables[0].Rows[0]["CountShopID"].ToString() != ""))
            {
                count.CountShopID = int.Parse(set.Tables[0].Rows[0]["CountShopID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountCreateTime"] != null) && (set.Tables[0].Rows[0]["CountCreateTime"].ToString() != ""))
            {
                count.CountCreateTime = DateTime.Parse(set.Tables[0].Rows[0]["CountCreateTime"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountUserID"] != null) && (set.Tables[0].Rows[0]["CountUserID"].ToString() != ""))
            {
                count.CountUserID = int.Parse(set.Tables[0].Rows[0]["CountUserID"].ToString());
            }
            if ((set.Tables[0].Rows[0]["CountIsCard"] != null) && (set.Tables[0].Rows[0]["CountIsCard"].ToString() != ""))
            {
                if ((set.Tables[0].Rows[0]["CountIsCard"].ToString() == "1") || (set.Tables[0].Rows[0]["CountIsCard"].ToString().ToLower() == "true"))
                {
                    count.CountIsCard = true;
                }
                else
                {
                    count.CountIsCard = false;
                }
            }
            if ((set.Tables[0].Rows[0]["CountIsCash"] != null) && (set.Tables[0].Rows[0]["CountIsCash"].ToString() != ""))
            {
                if ((set.Tables[0].Rows[0]["CountIsCash"].ToString() == "1") || (set.Tables[0].Rows[0]["CountIsCash"].ToString().ToLower() == "true"))
                {
                    count.CountIsCash = true;
                }
                else
                {
                    count.CountIsCash = false;
                }
            }
            if ((set.Tables[0].Rows[0]["CountIsBink"] != null) && (set.Tables[0].Rows[0]["CountIsBink"].ToString() != ""))
            {
                if ((set.Tables[0].Rows[0]["CountIsBink"].ToString() == "1") || (set.Tables[0].Rows[0]["CountIsBink"].ToString().ToLower() == "true"))
                {
                    count.CountIsBink = true;
                }
                else
                {
                    count.CountIsBink = false;
                }
            }
            if ((set.Tables[0].Rows[0]["CountPayBink"] != null) && (set.Tables[0].Rows[0]["CountPayBink"].ToString() != ""))
            {
                count.CountPayBink = decimal.Parse(set.Tables[0].Rows[0]["CountPayBink"].ToString());
            }
            return count;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM MemCount ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public decimal GetTotalCash(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select sum(CountPayCash) from MemCount where 1=1 ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" and " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0M;
            }
            return Convert.ToDecimal(single);
        }

        public bool Update(Chain.Model.MemCount model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update MemCount set ");
            builder.Append("CountMemID=@CountMemID,");
            builder.Append("CountAccount=@CountAccount,");
            builder.Append("CountTotalMoney=@CountTotalMoney,");
            builder.Append("CountDiscountMoney=@CountDiscountMoney,");
            builder.Append("CountPayCard=@CountPayCard,");
            builder.Append("CountPayCash=@CountPayCash,");
            builder.Append("CountPayCoupon=@CountPayCoupon");
            builder.Append("CountPayType=@CountPayType,");
            builder.Append("CountPoint=@CountPoint,");
            builder.Append("CountRemark=@CountRemark,");
            builder.Append("CountShopID=@CountShopID,");
            builder.Append("CountCreateTime=@CountCreateTime,");
            builder.Append("CountUserID=@CountUserID,");
            builder.Append("CountIsCard=@CountIsCard,");
            builder.Append("CountIsCash=@CountIsCash,");
            builder.Append("CountIsBink=@CountIsBink,");
            builder.Append("CountPayBink=@CountPayBink");
            builder.Append(" where CountID=@CountID");
            SqlParameter[] cmdParms = new SqlParameter[] { 
                new SqlParameter("@CountMemID", SqlDbType.Int, 4), new SqlParameter("@CountAccount", SqlDbType.VarChar, 50), new SqlParameter("@CountTotalMoney", SqlDbType.Money, 8), new SqlParameter("@CountDiscountMoney", SqlDbType.Money, 8), new SqlParameter("@CountPayCard", SqlDbType.Money, 8), new SqlParameter("@CountPayCash", SqlDbType.Money, 8), new SqlParameter("@CountPayCoupon", SqlDbType.Money, 8), new SqlParameter("@CountPayType", SqlDbType.TinyInt, 1), new SqlParameter("@CountPoint", SqlDbType.Int, 4), new SqlParameter("@CountRemark", SqlDbType.NVarChar, 500), new SqlParameter("@CountShopID", SqlDbType.Int, 4), new SqlParameter("@CountCreateTime", SqlDbType.DateTime), new SqlParameter("@CountUserID", SqlDbType.Int, 4), new SqlParameter("@CountIsCard", SqlDbType.Bit), new SqlParameter("@CountIsCash", SqlDbType.Bit), new SqlParameter("@CountIsBink", SqlDbType.Bit), 
                new SqlParameter("@CountPayBink", SqlDbType.Money, 8), new SqlParameter("@CountID", SqlDbType.Int, 4)
             };
            cmdParms[0].Value = model.CountMemID;
            cmdParms[1].Value = model.CountAccount;
            cmdParms[2].Value = model.CountTotalMoney;
            cmdParms[3].Value = model.CountDiscountMoney;
            cmdParms[4].Value = model.CountPayCard;
            cmdParms[5].Value = model.CountPayCash;
            cmdParms[6].Value = model.CountPayCoupon;
            cmdParms[7].Value = model.CountPayType;
            cmdParms[8].Value = model.CountPoint;
            cmdParms[9].Value = model.CountRemark;
            cmdParms[10].Value = model.CountShopID;
            cmdParms[11].Value = model.CountCreateTime;
            cmdParms[12].Value = model.CountUserID;
            cmdParms[13].Value = model.CountID;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

